# -*- coding: utf-8 -*-
# MySQL工具类
# @Time    : 2024/12/9 上午4:42
# @Author  : 赵鑫源
# @FileName: mysql_helper.py
# @Software: PyCharm

import mysql.connector
from mysql.connector import Error, pooling
import my_logging
from typing import List, Dict, Any, Optional, Union

# 初始化日志
logging = my_logging.LogUtils().get_log()


class MySQLHelper:
    def __init__(self):
        self.name = 'MySQLHelper'
        self.config = {
            'pool_name': 'my_pool',
            'pool_size': 20,  # 连接池大小
            'pool_reset_session': True,
            'user': 'root',
            'password': 'SmsePjFFy6DfD3DR',
            'host': '192.168.20.5',
            'port': 13307,
            'database': 'zhongao'
        }
        self.connection_pool = None
        self._init_pool()

    def _init_pool(self):
        """初始化连接池"""
        try:
            self.connection_pool = pooling.MySQLConnectionPool(**self.config)
        except Error as e:
            logging.error('MySQL 连接池初始化失败', e)
            raise

    def get_connection(self):
        """从连接池获取一个连接"""
        try:
            connection = self.connection_pool.get_connection()
            return connection
        except Error as e:
            logging.error('获取数据库连接失败', e)
            raise

    def execute_query(self, sql: str, params: tuple = ()) -> Optional[List[Dict[str, Any]]]:
        """
        执行查询语句
        :param sql: SQL 查询语句
        :param params: 查询参数
        :return: 查询结果（字典列表）
        """
        try:
            with self.get_connection() as connection:
                with connection.cursor(dictionary=True) as cursor:
                    cursor.execute(sql, params)
                    return cursor.fetchall()
        except Error as e:
            logging.error(f'执行查询失败: {sql}', e)
            return None

    def execute_update(self, sql: str, params: tuple = ()) -> Optional[int]:
        """
        执行更新语句
        :param sql: SQL 更新语句
        :param params: 更新参数
        :return: 受影响的行数
        """
        try:
            with self.get_connection() as connection:
                with connection.cursor() as cursor:
                    cursor.execute(sql, params)
                    connection.commit()
                    return cursor.rowcount
        except Error as e:
            logging.error(f'执行更新失败: {sql}', e)
            return None

    def execute_insert(self, sql: str, params: tuple = ()) -> Optional[int]:
        """
        执行插入语句
        :param sql: SQL 插入语句
        :param params: 插入参数
        :return: 插入的主键 ID
        """
        try:
            with self.get_connection() as connection:
                with connection.cursor() as cursor:
                    cursor.execute(sql, params)
                    connection.commit()
                    return cursor.lastrowid
        except Error as e:
            logging.error(f'执行插入失败: {sql}', e)
            return None

    def execute_batch(self, sql: str, params_list: List[tuple]) -> Optional[int]:
        """
        批量执行 SQL 语句
        :param sql: SQL 语句
        :param params_list: 参数列表
        :return: 受影响的行数
        """
        try:
            with self.get_connection() as connection:
                with connection.cursor() as cursor:
                    cursor.executemany(sql, params_list)
                    connection.commit()
                    return cursor.rowcount
        except Error as e:
            logging.error(f'批量执行失败: {sql}', e)
            return None

    def execute_transaction(self, sql_list: List[str], params_list: List[tuple]) -> bool:
        """
        执行事务
        :param sql_list: SQL 语句列表
        :param params_list: 参数列表
        :return: 是否成功
        """
        try:
            with self.get_connection() as connection:
                with connection.cursor() as cursor:
                    for sql, params in zip(sql_list, params_list):
                        cursor.execute(sql, params)
                    connection.commit()
                    return True
        except Error as e:
            logging.error('事务执行失败', e)
            connection.rollback()
            return False
